﻿using Dapper;
using Domain.Entity.Basic_information;
using Domain.Entity.System_operation;
using Domain.IRepository.Basic_information;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.DependencyInjection;

namespace EFCore.Repository.Basic_information
{
    [Dependency(ServiceLifetime.Scoped)]
    public class Ct_Repository:ICt_Repository
    {
        public MyDbContext myDbContext { get; set; }

        public Ct_Repository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }
        public int Count(string? Create_time, string? value2)
        {
            string sql = "select * from [dbo].[Customer_ment] where Is_del=0";
            if (Create_time != null && Create_time != "")
            {
                string[] time1 = Create_time.Split(',');
                sql += $" and [Create_time]>='{time1[0]}' and [Create_time]<='{time1[1]}'";
            }
            if (value2 != null && value2 != "")
            {
                sql += $"AND (Ct_cl_name LIKE '%{value2}%')";
            }
            int count = myDbContext.Database.GetDbConnection().Query<Customer_ment>(sql).Count();
            return count;
        }
        public List<Customer_ment> GetCustomer_ments(int currentPage, int PageSize)
        {
            List<Customer_ment> list = myDbContext.customer_Ments.Where(e => e.Is_del == 0).ToList();
            var ctList = list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return ctList;
        }
        //删除
        public bool DelCt(int id)
        {
            string sql = $"update [dbo].[Customer_ment] set Is_del=1 where Ct_id={id}";
            return myDbContext.Database.GetDbConnection().Execute(sql) > 0;
        }

        public int AddCt(Customer_ment customer)
        {
            string sql = $@"INSERT INTO dbo.Customer_ment(Ct_numbering, Ct_cl_name, Ct_lndustry, Ct_level, Ct_label, Ct_region, Ct_address, Ct_postcode, Ct_information, Ct_state, Ct_name, Ct_phone, Ct_department, Ct_posts, Ct_email, Ct_qq, Ct_io_title,Ct_tp_identification,Ct_bank,Ct_ao_name,Ct_account,Create_time,Is_del)
                        VALUES('{customer.Ct_numbering}','{customer.Ct_cl_name}','{customer.Ct_lndustry}','{customer.Ct_level}','{customer.Ct_label}','{customer.Ct_region}','{customer.Ct_address}','{customer.Ct_postcode}','{customer.Ct_information}','{customer.Ct_state}','{customer.Ct_name}','{customer.Ct_phone}','{customer.Ct_department}','{customer.Ct_posts}','{customer.Ct_email}','{customer.Ct_qq}','{customer.Ct_io_title}','{customer.Ct_tp_identification}','{customer.Ct_bank}','{customer.Ct_ao_name}','{customer.Ct_account}','{customer.Create_time}','0')";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public int UpdCt(Customer_ment customer_ment)
        {
            string sql = $@"UPDATE dbo.Customer_ment SET Ct_numbering='{customer_ment.Ct_numbering}', Ct_cl_name='{customer_ment.Ct_cl_name}', Ct_lndustry='{customer_ment.Ct_lndustry}', Ct_level='{customer_ment.Ct_level}', Ct_label='{customer_ment.Ct_label}', Ct_region='{customer_ment.Ct_region}', Ct_address='{customer_ment.Ct_address}', Ct_postcode='{customer_ment.Ct_postcode}', Ct_information='{customer_ment.Ct_information}', Ct_state='{customer_ment.Ct_state}', Ct_name='{customer_ment.Ct_name}', Ct_phone='{customer_ment.Ct_phone}', Ct_department='{customer_ment.Ct_department}', Ct_posts='{customer_ment.Ct_posts}', Ct_email='{customer_ment.Ct_email}', Ct_qq='{customer_ment.Ct_qq}', Ct_io_title='{customer_ment.Ct_io_title}',Ct_tp_identification='{customer_ment.Ct_tp_identification}',Ct_bank='{customer_ment.Ct_bank}',Ct_ao_name='{customer_ment.Ct_ao_name}',Ct_account='{customer_ment.Ct_account}',Update_time=GETDATE()
                        WHERE Is_del=0 AND Ct_id={customer_ment.Ct_id}";
                return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public Customer_ment GetCustomer(int id)
        {
            string sql = $"SELECT * FROM dbo.Customer_ment WHERE Is_del=0 AND Ct_id={id}";
            return myDbContext.Database.GetDbConnection().Query<Customer_ment>(sql).FirstOrDefault();
        }

        public List<Customer_ment> Customer_condition(int currentPage, int PageSize,string ?Create_time, string? value2)
        {
            string sql = $"select * from [dbo].[Customer_ment] where Is_del=0";
            if (Create_time != null && Create_time != "")
            {
                string[] time1 = Create_time.Split(',');
                sql += $" and [Create_time]>='{time1[0]}' and [Create_time]<='{time1[1]}'";
            }
            if (value2 != null && value2 != "")
            {
                sql += $"AND (Ct_cl_name LIKE '%{value2}%')";
            }
            List<Customer_ment> list = myDbContext.Database.GetDbConnection().Query<Customer_ment>(sql).ToList();
            var customers = list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return customers;
        }
    }
}
